﻿using System;
using System.Collections.Generic;
using System.Text;
using QLPMT_DTO;
using System.Data.SqlClient;
namespace QLPMT_DAO
{
   public class BenhNhanDAO
    {
           DataProvider provider;
           public  BenhNhanDAO()
           {
               provider = new DataProvider();
           }
           //lay danh sach tat ca cac benh nhan
           public List<BenhNhan> GetAll()
           {
               provider.Connect();
               string sql = "select * from benhnhan";
               SqlDataReader reader = provider.ExecuteReader(sql);
               List<BenhNhan> list = new List<BenhNhan>();
               while (reader.Read())
               {
                   string ma = reader.GetString(0);
                   string hoten = reader.GetString(1);
                   int gioitinh = (int)reader["Gioitinh"];
                   string tengioitinh="";
                   if (gioitinh == 0)
                       tengioitinh = "Nam";
                   else
                       tengioitinh = "Nữ";
                   int namsinh = reader.GetInt32(3);
                   string diachi = reader.GetString(4);
                   BenhNhan benhnhan= new BenhNhan(ma,hoten,tengioitinh,namsinh,diachi);
                   list.Add(benhnhan);

               }
               provider.Disconnect();
               return list;
           }
           //lay danh sach benh nhan vua tiep nhan hom nay
           public List<BenhNhan> GetBenhNhanVuaTiepNhan(DateTime NgayTiepNhan)
           {
               provider.Connect();
               string sql = "select benhnhan.* from benhnhan where datediff(dd,NgayTiepNhan,'" + NgayTiepNhan + "')=0";
               SqlDataReader reader = provider.ExecuteReader(sql);
               List<BenhNhan> list = new List<BenhNhan>();
               while (reader.Read())
               {
                   string ma = (string)reader["MaBenhNhan"];
                   string hoten = (string)reader["HoTen"];
                   int gioitinh = (int)reader["Gioitinh"];
                   string tengioitinh = "";
                   if (gioitinh == 0)
                       tengioitinh = "Nam";
                   else
                       tengioitinh = "Nữ";
                   int namsinh = (int)reader["NamSinh"];
                   string diachi = (string)reader["DiaChi"];
                   DateTime n = (DateTime)reader["NgayTiepNhan"];
                   BenhNhan benhnhan = new BenhNhan(ma, hoten, tengioitinh, namsinh, diachi,n);
                   list.Add(benhnhan);

               }
               provider.Disconnect();
               return list;
           }
       //lay danh sach benh nhan cung ngaykham
       public List<BenhNhan> GetBenhNhanTheoNgayKham(DateTime ngaykham)
       {
           provider.Connect();
           string sql = "select benhnhan.* from benhnhan,khambenh where datediff(dd,NgayKham,'"+ngaykham+"')=0 and benhnhan.mabenhnhan=khambenh.mabenhnhan";
           SqlDataReader reader = provider.ExecuteReader(sql);
           List<BenhNhan> list = new List<BenhNhan>();
           while (reader.Read())
           {
               string ma = (string)reader["MaBenhNhan"];
               string hoten = (string)reader["HoTen"];
               int gioitinh = (int)reader["Gioitinh"];
               string tengioitinh = "";
               if (gioitinh == 0)
                   tengioitinh = "Nam";
               else
                   tengioitinh = "Nữ";
               int namsinh = (int)reader["NamSinh"];
               string diachi = (string)reader["DiaChi"];
               BenhNhan benhnhan = new BenhNhan(ma, hoten, tengioitinh, namsinh, diachi);
               list.Add(benhnhan);

           }
           provider.Disconnect();
           return list;
       }
       //lay thong tin benh nhan theo ma kham benh
       public BenhNhan GetThongTinBenhNhanTheoMaKhamBenh(string makhambenh)
       {
           provider.Connect();
           string sql = "select benhnhan.* from benhnhan,khambenh where khambenh.makhambenh='"+makhambenh+"' and khambenh.mabenhnhan=benhnhan.mabenhnhan";
           SqlDataReader reader = provider.ExecuteReader(sql);
           BenhNhan benhnhan=new BenhNhan();
           if (reader.Read())
           {
               string ma = reader.GetString(0);
               string hoten = (string)reader["HoTen"].ToString();
               int gioitinh = (int)reader["Gioitinh"];
               string tengioitinh = "";
               if (gioitinh == 0)
                   tengioitinh = "Nam";
               else
                   tengioitinh = "Nữ";
               int namsinh = (int)reader["NamSinh"];
               string diachi = (string)reader["DiaChi"];
               benhnhan = new BenhNhan(ma, hoten, tengioitinh, namsinh, diachi);
           }
           
           provider.Disconnect();
           return benhnhan;
       }
       // Them benh nhan moi
       public void ThemBenhNhanMoi(BenhNhan BN)
       {
           provider.Connect();
           string sql="Insert into BenhNhan(MaBenhNhan,HoTen,GioiTinh,NamSinh,DiaChi,NgayTiepNhan) values('"+BN.Mabenhnhan+"','"+BN.Hoten+"',"+BN.Gioitinh+","+BN.Namsinh+",'"+BN.Diachi+"','"+BN.NgayTiepNhan+ "')";
           provider.ExecuteQuery(sql);
           provider.Disconnect();

       }
       // Tim kiem thong tin benh nhan
       public List<BenhNhan> Search(string ten,string diachi,int namsinh,string maloaibenh,string trieuchung,DateTime ngaybt,DateTime ngkt)
       {
           provider.Connect();
           List<BenhNhan> list = new List<BenhNhan>();
           string sql = "select distinct BenhNhan.*,PhieuKham.TrieuChung,Benh.LoaiBenh from benhnhan,chitietphieukham,phieukham,khambenh,benh where khambenh.mabenhnhan=benhnhan.mabenhnhan and khambenh.makhambenh=phieukham.makhambenh and chitietphieukham.maphieukham=phieukham.maphieukham and chitietphieukham.Maloaibenh=benh.Maloaibenh";
           if (trieuchung == "" && maloaibenh == "")
           {
                sql = "select distinct BenhNhan.* from benhnhan,khambenh where khambenh.mabenhnhan=benhnhan.mabenhnhan ";
                if (ngaybt.Year.ToString() == "1" && ngkt.Year.ToString() == "1")
                {
                    sql = "select distinct BenhNhan.* from benhnhan where namsinh>0";
                }
           }
         
           if (ten != "")
           {
               sql += " and benhnhan.Hoten like '%" + ten + "%'";
           }
           if (diachi != "")
           {
               sql += " and benhnhan.diachi like '%" + diachi + "%'";
           }
           if (namsinh > 0)
           {
               sql += " and benhnhan.Namsinh=" + namsinh;
           }
           if (maloaibenh != "")
           {
               sql += " and chitietphieukham.MaLoaiBenh='" + maloaibenh + "'";
           }
           if (trieuchung != "")
           {
               sql += " and PhieuKham.TrieuChung like '%" + trieuchung + "%'";
           }
          
           if (ngaybt.Year.ToString() != "1")
           {
               sql+=" and datediff(dd, NgayKham,'"+ngaybt.Date+"')<=0";
           }
           if(ngkt.Year.ToString()!="1")
           {
                  sql+=" and datediff(dd, NgayKham,'"+ngkt.Date+"')>=0";
           }
           SqlDataReader reader = provider.ExecuteReader(sql);
           if (trieuchung == "" && maloaibenh == "")
           {
               while (reader.Read())
               {
                   string _ma = (string)reader["Mabenhnhan"];
                   string _hoten = (string)reader["HoTen"];
                   int _gioitinh = (int)reader["GioiTinh"];
                   int _namsinh = (int)reader["NamSinh"];
                   string _diachi = (string)reader["diachi"];
                   BenhNhan dto = new BenhNhan(_ma, _hoten, _gioitinh, _namsinh, _diachi);
                   list.Add(dto);
               }
           }
           else
           {
               while (reader.Read())
               {
                   string _ma = (string)reader["Mabenhnhan"];
                   string _hoten = (string)reader["HoTen"];
                   int _gioitinh = (int)reader["GioiTinh"];
                   int _namsinh = (int)reader["NamSinh"];
                   string _diachi = (string)reader["diachi"];
                   string _loaibenh = (string)reader["Loaibenh"];
                   string _trieuchung = (string)reader["TrieuChung"];
                   BenhNhan dto = new BenhNhan(_ma, _hoten, _gioitinh, _namsinh, _diachi, _loaibenh, _trieuchung);
                   list.Add(dto);
               }
           }
           provider.Disconnect();
           return list;
       }
    }
}
